<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Reading data with SqliteDataReader</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Visual Basic, SQLite, ADO.NET, database, SqliteDataReader, SqliteCommand">
<meta name="description" content="In this chapter of the SQLite Visual Basic tutorial, we read
data from the database using the SQLiteDataReader class. ">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Retrieving data with SqliteDataReader</h1>

<p>
The <b>SqliteDataReader</b> is a class used
to retrieve data from the database.  It is used with the
<b>SqliteCommand</b> class to execute an SQL SELECT statement
and then access the returned rows. It provides fast, forward-only, 
read-only access to query results. It is the most efficient way
to retrieve data from tables. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Rather than using a constructor, we create an instance of the
SqliteDataReader by calling the ExecuteReader() method of the 
SqliteCommand object. While the SqlDataReader is being used, 
the associated SqlConnection serves the SqlDataReader. No other 
operations can be performed on the SqlConnection  other than 
closing it. 
</p>


<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)        
            con.Open()
        
            Using cmd As New SqliteCommand(con)

                cmd.CommandText = "SELECT * FROM Cars LIMIT 5"
                
                Dim rdr As SqliteDataReader = cmd.ExecuteReader()

                Using rdr                
                    While (rdr.Read())
                        Console.WriteLine(rdr.GetInt32(0) &amp; " " _ 
                            &amp; rdr.GetString(1) &amp; " " &amp; rdr.GetInt32(2))
                    End While         
                End Using        
            End Using

            con.Close()
        End Using
        
    End Sub

End Module
</pre>

<p>
We get 5 cars from the Cars table and print them to 
the console. 
</p>

<pre class="explanation">
Dim rdr As SqliteDataReader = cmd.ExecuteReader()
</pre>

<p>
To create an <code>SQLiteDataReader</code> object, we must call 
the <code>ExecuteReader()</code> method of the 
<code>SqliteCommand</code> object.
</p>

<pre class="explanation">
While (rdr.Read())
    Console.WriteLine(rdr.GetInt32(0) &amp; " " _ 
        &amp; rdr.GetString(1) &amp; " " &amp; rdr.GetInt32(2))
End While
</pre>

<p>
The <code>Read()</code> method advances the data reader to the next record.
It returns true if there are more rows; otherwise false. We can retrieve the value using 
the array index notation, or use a specific method to access column values in their 
native data types. The latter is more efficient. 
</p>

<pre>
$ mono retrieve.exe 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
</pre>

<p>
The first five rows of the Cars table.
</p>

<hr class="btm">

<p>
We can retrieve the fields by their column names. 
</p>

<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)        
            con.Open()
        
            Using cmd As New SqliteCommand(con)
                cmd.CommandText = "SELECT * FROM Cars LIMIT 5"
                
                Dim rdr As SqliteDataReader = cmd.ExecuteReader()

                Using rdr
                    While (rdr.Read())
                        Console.Write("{0} ", rdr("Id"))
                        Console.Write("{0} ", rdr("Name"))
                        Console.WriteLine("{0} ", rdr("Price"))
                    End While         
                End Using

            End Using
            con.Close()
        End Using
        
    End Sub

End Module
</pre>

<p>
The example prints 5 rows from the Cars table. This time we use 
the column names to get the table fields. 
</p>

<pre class="explanation">
While (rdr.Read())
    Console.Write("{0} ", rdr("Id"))
    Console.Write("{0} ", rdr("Name"))
    Console.WriteLine("{0} ", rdr("Price"))
End While
</pre>

<p>
The database table fields are referenced by their column
names. 
</p>


<h2>Multiple statements</h2>

<p>
The ADO.NET specification allows to execute multiple
statements in a single string. In case of queries, the
<code>SqliteDataReader</code> returns multiple result sets.
It has the <code>NextResult()</code> method to navigate 
throught the result sets. 
</p>


<pre class="code">
Option Strict On

Imports Mono.Data.Sqlite

Module Example

    Sub Main()

        Dim cs As String = "URI=file:test.db"

        Using con As New SqliteConnection(cs)       
            con.Open()
        
            Using cmd As New SqliteCommand(con)
                cmd.CommandText = "SELECT 25; SELECT 44; SELECT 33"
                
                Dim rdr As SqliteDataReader = cmd.ExecuteReader()

                Using rdr                
                    Do                    
                        rdr.Read()
                        Console.WriteLine("{0}", rdr.GetInt32(0))
                    Loop While rdr.NextResult()         
                End Using      
            End Using

            con.Close()
        End Using
        
    End Sub

End Module
</pre>

<p>
We have three queries in one SQL string. There will be three result
sets. 
</p>

<pre class="explanation">
cmd.CommandText = "SELECT 25; SELECT 44; SELECT 33"
</pre>

<p>
There are three SELECT statements. They are separated by the semicolon
character. Each of them will return a single value.
</p>

<pre class="explanation">
Do                    
    rdr.Read()
    Console.WriteLine("{0}", rdr.GetInt32(0))

Loop While rdr.NextResult()
</pre>

<p>
The <code>Read()</code> method advances the <code>SqliteDataReader</code> 
to the next record. The <code>GetInt32()</code> method retrieves the
value as a 32-bit signed integer. The <code>NextResult()</code> 
advances the data reader to the next result.
</p>

<pre>
$ mono multiple.exe 
25
44
33
</pre>

<p>
Running the example.
</p>

<p>
We have finished reading data with the SqliteDataReader.
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 28, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

